S3からRedShiftへデータをロードするチュートリアルをやってみた

1時間程度でRedshiftにデータをロードするチュートリアルができます。Redshift初心者におすすめなのでこれから勉強したい人は是非やってみましょう。

#Amazon S3

#Amazon Redshift

#AWS

鈴木純

2021.01.05

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、鈴木(純)です。

Redshift勉強していてちょうどよくS3からデータをロードするチュートリアルがあったのでやってみました。

はじめに

今回やるのはこちらのチュートリアルです。

Redshiftのクラスターを起動して、サンプルのS3をアップロード。起動したRedshiftからサンプルデータをロードするところまでをこのチュートリアルでは体験できます。

やってみた

それではさっそくやっていきます。

1. IAMロールを作成する

まずは前準備として、RedShiftのクラスターへS3へのアクセスを許可するロールを作成します。

AWSサービスの中からRedshiftを選択します。

クリックすると、自動でスクロールされてユースケースの選択画面へ移動するので、Redshiftを選択して次へ。

今回はチュートリアルなので、とりあえずS3フルアクセスのポリシーをアタッチします。このチュートリアル上ではGetとListの権限があれば問題ありません。

ロール名を入力してロールを作成します。

2. クラスターを作成する

Redshiftの画面へ進むと、クラスターを作成ボタンがあるのでそこから作成していきます。

無料トライアルにチェックを入れてデータベース設定はマスターユーザーのパスワード以外はデフォルトで進めます。

クラスターのアクセス許可を与える項目では、先ほど作成したIAMロールを選択してから「IAMロールの追加」をクリックしてください。

その他はデフォルトのままクラスターを作成します。

3. サンプルデータファイルのダウンロード

今回はチュートリアルのため、サンプル用のデータをダウンロードしましょう。

以下のリンクからサンプルデータをダウンロードすることができます。

LoadingDataSampleFiles.zip

Zipファイルを展開すると以下のようなサンプルファイルを確認することができます。

4. ファイルを Amazon S3 バケットへアップロードする

このステップではダウンロードしたサンプルファイルをS3にアップロードします。

今回はチュートリアル用のS3バケット「redshift-tutorial-bucket」を作成しました。Redshiftのリージョンと同じリージョンで作成するようにしてください。今回はオレゴンリージョンで実行しているため、S3もオレゴンに作成しています。バケット名以外はデフォルトで問題ないです。

バケットを作成したら、そのバケットの中に「load」という名前でフォルダを作成しましょう。

上で作成したloadファイルをS3上で開いて先ほどダウンロードしたサンプルファイルをアップロードしましょう。アップロードする時は、サンプルファイルをまとめて選択してアップロードできます。

アップロードが完了を確認しましょう。

5. サンプルテーブルの作成

それではRedshiftへサンプルテーブルを作成していきます。

今回はpsqlを使ってRedshiftへアクセスしてみます。psqlの場合は以下を書き換えて接続できます。

psql -h <endpoint> -U <userid> -d <databasename> -p <port>

エンドポイントはクラスターの画面から確認できます。これをコピーすると、endpoint:port/databasenameの形式でコピーされます。

正しく接続できればパスワードの入力が求められるので、最初に設定したパスワードを入力しましょう。

接続できたら、以下のコマンドを実行してサンプルテーブルを作成します。

CREATE TABLE part 
(
  p_partkey     INTEGER NOT NULL,
  p_name        VARCHAR(22) NOT NULL,
  p_mfgr        VARCHAR(6),
  p_category    VARCHAR(7) NOT NULL,
  p_brand1      VARCHAR(9) NOT NULL,
  p_color       VARCHAR(11) NOT NULL,
  p_type        VARCHAR(25) NOT NULL,
  p_size        INTEGER NOT NULL,
  p_container   VARCHAR(10) NOT NULL
);

CREATE TABLE supplier 
(
  s_suppkey   INTEGER NOT NULL,
  s_name      VARCHAR(25) NOT NULL,
  s_address   VARCHAR(25) NOT NULL,
  s_city      VARCHAR(10) NOT NULL,
  s_nation    VARCHAR(15) NOT NULL,
  s_region    VARCHAR(12) NOT NULL,
  s_phone     VARCHAR(15) NOT NULL
);

CREATE TABLE customer 
(
  c_custkey      INTEGER NOT NULL,
  c_name         VARCHAR(25) NOT NULL,
  c_address      VARCHAR(25) NOT NULL,
  c_city         VARCHAR(10) NOT NULL,
  c_nation       VARCHAR(15) NOT NULL,
  c_region       VARCHAR(12) NOT NULL,
  c_phone        VARCHAR(15) NOT NULL,
  c_mktsegment   VARCHAR(10) NOT NULL
);

CREATE TABLE dwdate 
(
  d_datekey            INTEGER NOT NULL,
  d_date               VARCHAR(19) NOT NULL,
  d_dayofweek          VARCHAR(10) NOT NULL,
  d_month              VARCHAR(10) NOT NULL,
  d_year               INTEGER NOT NULL,
  d_yearmonthnum       INTEGER NOT NULL,
  d_yearmonth          VARCHAR(8) NOT NULL,
  d_daynuminweek       INTEGER NOT NULL,
  d_daynuminmonth      INTEGER NOT NULL,
  d_daynuminyear       INTEGER NOT NULL,
  d_monthnuminyear     INTEGER NOT NULL,
  d_weeknuminyear      INTEGER NOT NULL,
  d_sellingseason      VARCHAR(13) NOT NULL,
  d_lastdayinweekfl    VARCHAR(1) NOT NULL,
  d_lastdayinmonthfl   VARCHAR(1) NOT NULL,
  d_holidayfl          VARCHAR(1) NOT NULL,
  d_weekdayfl          VARCHAR(1) NOT NULL
);
CREATE TABLE lineorder 
(
  lo_orderkey          INTEGER NOT NULL,
  lo_linenumber        INTEGER NOT NULL,
  lo_custkey           INTEGER NOT NULL,
  lo_partkey           INTEGER NOT NULL,
  lo_suppkey           INTEGER NOT NULL,
  lo_orderdate         INTEGER NOT NULL,
  lo_orderpriority     VARCHAR(15) NOT NULL,
  lo_shippriority      VARCHAR(1) NOT NULL,
  lo_quantity          INTEGER NOT NULL,
  lo_extendedprice     INTEGER NOT NULL,
  lo_ordertotalprice   INTEGER NOT NULL,
  lo_discount          INTEGER NOT NULL,
  lo_revenue           INTEGER NOT NULL,
  lo_supplycost        INTEGER NOT NULL,
  lo_tax               INTEGER NOT NULL,
  lo_commitdate        INTEGER NOT NULL,
  lo_shipmode          VARCHAR(10) NOT NULL
);

6. COPYコマンドの実行

S3バケットからRedshiftへデータをロードしていきます。

S3への認証は最初に作成したIAMロールを使用していきます。コマンド内のiam_roleは適宜置き換えて実行してください。

partテーブルのロード

partテーブルでロードするファイルはcsv形式のためオプションでcsvを指定します。今回のサンプルデータの中にはNULL値が含まれているため、NULL AS オプションを使用してロードを行います。

copy part from 's3://<your-bucket-name>/load/part-csv.tbl' 
iam_role 'arn:aws:iam::111111111111:role/Redshift-Tutorial-Role'
csv
null as '\000';

以下のコマンドでNULLが含まれている行を確認することができます。

$ select p_partkey, p_name, p_mfgr, p_category from part where p_mfgr is null;
 p_partkey |  p_name  | p_mfgr | p_category
-----------+----------+--------+------------
        15 | NUL next |        | MFGR#47
        81 | NUL next |        | MFGR#23
       133 | NUL next |        | MFGR#44
(3 rows)

supplierテーブルのロード

このチュートリアルでは、自分で作成したバケットではなくAWSで用意されているバケットからデータをロードしているため、バケット名はそのままで大丈夫です。

copy supplier from 's3://awssampledbuswest2/ssbgz/supplier.tbl' 
iam_role 'arn:aws:iam::111111111111:role/Redshift-Tutorial-Role' 
delimiter '|' 
gzip
region 'us-west-2';

customerテーブルのロード

customerテーブルのロードではS3バケットの中から必要なファイルのみをロードするために、MANIFESTを使用します。サンプルデータのファイルにはロードしたいcustomer-fw.tbl-000,

customer-fw.tbl-001といったファイルの他に、ロードしたくないcustomer-fw.tbl-000.bakやcustomer-fw.tbl.logといったファイルが混在しています。そのためustomer-fw-manifestのファイルを編集して、正しいロード対象を記載していきます。

S3バケットのサンプルデータの中に、customer-fw-manifestがあるはずなので、こちらをダウンロードしてください。

ファイルを開くと、以下のようなJSONが確認できるので、<my-bucket>を自分で作成したバケット名に置き換えて上書き保存します。

{
  "entries": [
    {"url":"s3://<my-bucket>/load/customer-fw.tbl-000"},
    {"url":"s3://<my-bucket>/load/customer-fw.tbl-001"},
    {"url":"s3://<my-bucket>/load/customer-fw.tbl-002"},
    {"url":"s3://<my-bucket>/load/customer-fw.tbl-003"},
    {"url":"s3://<my-bucket>/load/customer-fw.tbl-004"},    
    {"url":"s3://<my-bucket>/load/customer-fw.tbl-005"},
    {"url":"s3://<my-bucket>/load/customer-fw.tbl-006"}, 
    {"url":"s3://<my-bucket>/load/customer-fw.tbl-007"}
 ]
}

データをロードする時にはmanifestのオプションを使用しましょう。

copy customer from 's3://<your-bucket-name>/load/customer-fw-manifest'
iam_role 'arn:aws:iam::111111111111:role/Redshift-Tutorial-Role' 
fixedwidth 'c_custkey:10, c_name:25, c_address:25, c_city:10, c_nation:15, c_region :12, c_phone:15,c_mktsegment:10'
maxerror 10 
acceptinvchars as '^'
manifest;

maxerrorはテスト中最初のロードではエラーが予想されるため、それを許容するオプションです。このオプションをつけることで、エラーが発生した場合でもロードを続行します。

acceptinvcharsはデータ型でサポートされていない文字を検出した場合、有効な文字に置き換えてロードを続行するオプションです。

dwdateテーブルのロード

copy dwdate from 's3://<your-bucket-name>/load/dwdate-tab.tbl'
iam_role 'arn:aws:iam::111111111111:role/Redshift-Tutorial-Role' 
delimiter '\t' 
dateformat 'auto';

dateformatでautoを使用すると、ロードデータに一貫性がない場合の日付を自動的に認識して変換してくれます。詳細は以下ドキュメントを参照してください。

lineorderテーブルのロード

AWSで用意されているバケットを使用します。credentialsだけ書き換えて実行してください。このテーブルでは1つのファイルからロードする場合と、複数ファイルからロードする場合で実行時間に差があることを確認できます。

ステップ 5. COPYコマンドの実行より引用

以下二つのコマンドを実行すると、ノード数によるロードの速さが比較できます。今回はノード数１のためほとんど実行時間に差はありませんでした。

copy lineorder from 's3://awssampledb/load/lo/lineorder-single.tbl' 
iam_role 'arn:aws:iam::111111111111:role/Redshift-Tutorial-Role'
gzip
compupdate off
region 'us-east-1';

copy lineorder from 's3://awssampledb/load/lo/lineorder-multi.tbl' 
iam_role 'arn:aws:iam::111111111111:role/Redshift-Tutorial-Role'
gzip
compupdate off
region 'us-east-1';

これで全テーブルにサンプルデータをS3からロードすることができました。自由にSELECTなどを実行してテーブルを確認してみてください。